--SETUP

--DROP TABLE Example
CREATE TABLE Example (
	Id INT NOT NULL PRIMARY KEY CLUSTERED,
	Foo	varchar(50) NULL,
	Bar varchar(50) NULL,
	Bux varchar(50) NULL
)
GO


--we decide later to add an index to Foo due to performance problems
--we "check-in" the following code
select * from MD3.f_GetIndexes  ('dbo','Example')	
	
EXEC MD3.CreateIndex 
	@SchemaName = 'dbo',
	@TableName = 'Example',
	@IndexName = 'nciFoo',
	@IndexKeyColumns = 'Foo',
	@FileGroupOrPartition = 'PRIMARY',
	@IsClustered = 0,
	@IncludedColumns = NULL,
	@FillFactor = 100,
	@PadIndex = 0 ,
	@DropIndex = 0 ,
	@DataCompressionType = 'ROW';


--automatically handles re-runnability for you.  (Rule 1)

--Rule 2:  Everything exists by property, but the name is wrong;
EXEC MD3.CreateIndex 
	@SchemaName = 'dbo',
	@TableName = 'Example',
	@IndexName = 'MyNciFoo',
	@IndexKeyColumns = 'Foo',
	@FileGroupOrPartition = 'PRIMARY',
	@IsClustered = 0,
	@IncludedColumns = NULL,
	@FillFactor = 100,
	@PadIndex = 0 ,
	@DropIndex = 0 ,
	@DataCompressionType = 'ROW';
select * from MD3.f_GetIndexes  ('dbo','Example')	

--Rule 3:  Index exists by name, but let's change some properties.  (@DataCompressionType in this case)
select * from MD3.f_GetIndexes  ('dbo','Example')	
EXEC MD3.CreateIndex 
	@SchemaName = 'dbo',
	@TableName = 'Example',
	@IndexName = 'MyNciFoo',
	@IndexKeyColumns = 'Foo',
	@FileGroupOrPartition = 'PRIMARY',
	@IsClustered = 0,
	@IncludedColumns = NULL,
	@FillFactor = 100,
	@PadIndex = 0 ,
	@DropIndex = 0 ,
	@DataCompressionType = 'NONE';
select * from MD3.f_GetIndexes  ('dbo','Example')	

--change some other properties


